﻿using System;
using System.Data;
using System.Configuration;

using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

using DAL.DHelpers;
using DAL.DFactory;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data.Odbc;
using MySql.Data.MySqlClient;


namespace DAL
{
    public class DatabaseManager:IDisposable
    {
        #region Thiết lập các chức năng , thuộc tính cho hệ CSDL
        private DatabaseProvider provider;
        public DatabaseProvider Provider
        {
            get { return provider; }
            set { provider = value; }
        }
        private IDbConnection connection;
        public IDbConnection Connection
        {
            get { return connection; }
        }

        private IDataReader reader;
        public IDataReader Reader
        {
            get { return reader; }
        }
      
        private IDbCommand command;
        private DatabaseObject database = null;

        public string ConnectionString
        {
            get { return ConnectionHelper.GetConnectionString(); }
        }
        public string ProviderName
        {
            get { return ConnectionHelper.GetProviderName(); }
        }
        //Phần dữ liệu cho bảng tham chiếu trong CSDL
        private Dictionary<String, MappingTable> tables = new Dictionary<String, MappingTable>();
        public Dictionary<String, MappingTable> Tables
        {
            set { tables = value; }
            get { return tables; }
        }
        private static Dictionary<String, DatabaseObject> instances = new Dictionary<String, DatabaseObject>();
        #endregion   
       
        #region Khởi tạo các thể hiện tĩnh cho mỗi hệ CSDL
        public static DatabaseManager SqlServerInstance = new DatabaseManager(DatabaseProvider.SQLServer);
        public static DatabaseManager OracleInstance = new DatabaseManager(DatabaseProvider.Oracle);
        public static DatabaseManager AccessInstance = new DatabaseManager(DatabaseProvider.Access);
        public static DatabaseManager MySqlInstance = new DatabaseManager(DatabaseProvider.MySQL);
        #endregion

        #region Khởi tạo tùy theo hệ CSDL
        public DatabaseManager(DatabaseProvider provider)
        {
            this.provider = provider;

            switch (provider)
            {
                case DatabaseProvider.Oracle:
                    {
                        database = GetInstance(DatabaseProvider.Oracle);
                        break;
                    }

                case DatabaseProvider.SQLServer:
                    {
                        database = GetInstance(DatabaseProvider.SQLServer);
                        break;
                    }
                case DatabaseProvider.Access:
                    {
                        database = GetInstance(DatabaseProvider.Access);
                        break;
                    }
                case DatabaseProvider.MySQL:
                    {
                        database = GetInstance(DatabaseProvider.MySQL);
                        break;
                    }
                default:
                    break;
            }
            //InitTables();
        }
        #endregion

        #region Quan ly cac the hien cua CSDL
        public static void Reset()
        {
            instances = new Dictionary<String, DatabaseObject>();
        }

        private  static DatabaseObject GetInstance(DatabaseProvider provider)
        {
            DatabaseObject newInstance = null;
            string name = provider.ToString();
            if (instances.ContainsKey(name))
                return instances[name];
            else
            {
                switch (provider)
                {
                    case DatabaseProvider.Oracle:
                        {
                            newInstance = new DOracle();
                            break;
                        }

                    case DatabaseProvider.SQLServer:
                        {
                            newInstance = new DSqlServer();
                            break;
                        }
                    case DatabaseProvider.Access:
                        {
                            newInstance = new DAccess();
                            break;
                        }
                    case DatabaseProvider.MySQL:
                        {
                            newInstance = new DMySql();
                            break;
                        }
                    default:
                        break;
                }
                
                instances.Add(name, newInstance);
                return newInstance;
            }
        }
        #endregion

        #region Các thao tác với bảng tham chiếu trong CSDL
        public MappingTable GetTable(String name)
        {
            if (this.tables.ContainsKey(name))
                return this.tables[name];
            else
            {
                MappingTable table = new MappingTable(name,true);
                this.tables.Add(name, table);
                return table;
            }
        }
        public void InitTables()
        {
            this.tables = new Dictionary<String, MappingTable>();
            DataTable table = ExecuteDataTable("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'");
            for (int i = 0; i < table.Rows.Count; i++)
                this.GetTable(table.Rows[i]["TABLE_NAME"].ToString());
        }

       
        #endregion

        #region Phương thức kết nối , truy suất  CSDL

        #region Open
        public void Open()
        {
            
            connection = database.CreateConnection(ConnectionString);
            if (connection.State != ConnectionState.Open && connection.State != ConnectionState.Connecting)
            {
                connection.Open();
                command = database.CreateCommand();
            }
            else
            {
                Close();
                Dispose();
            }

            
        }
        #endregion

        #region ExecuteReader
        public IDataReader ExecuteReader(string query)
        {
            Open();
            command = database.CreateCommand(query, Connection);
            reader = command.ExecuteReader();
            Close();
            return reader;
        }
        #endregion

        #region ExecuteDataSet
        public DataSet ExecuteDataSet(string query)
        {
            Open();
            command = database.CreateCommand(query, Connection);

            IDbDataAdapter adapter = database.CreateDataAdapter();
            adapter.SelectCommand = command;

            DataSet ds = new DataSet();
            adapter.Fill(ds);
            Close();
            return ds;
        }
        #endregion

        #region ExecuteDataTable
        public DataTable ExecuteDataTable (String sql)
        {
            Open();
            command = database.CreateCommand(sql, Connection);

            IDbDataAdapter adapter = database.CreateDataAdapter();
            adapter.SelectCommand = command;

            DataSet ds = new DataSet();
            adapter.Fill(ds);
            Close();
            return ds.Tables[0];
        }
        public DataTable ExecuteDataTable(string query, MySqlParameter[] p)
        {
            Open();
            command = database.CreateCommand(query, Connection);
            for (int i = 0; i < p.Length; i++)
                command.Parameters.Add(p[i]);

            IDbDataAdapter adapter = database.CreateDataAdapter();
            adapter.SelectCommand = command;

            DataSet ds = new DataSet();
            adapter.Fill(ds);
            Close();
            return ds.Tables[0];
            
           
        }
        public DataTable ExecuteDataTable(String sql, String[] parNames, object[] parValues)
        {
            return null;
        }
        #endregion

        #region ExecuteScalar
        public object ExecuteScalar(string query)
        {
            Open();
            command = database.CreateCommand(query, Connection);
            object obj = command.ExecuteScalar();
            Close();
            return obj;
        }
        #endregion

        #region ExecuteNonQuery
        public int ExecuteNonQuery(string query)
        {
            Open();
            command = database.CreateCommand(query, Connection);
            int fields = command.ExecuteNonQuery();
            Close();
            return fields;
        }
        public int ExecuteNonQuery(string query, MySqlParameter [] p)
        {
            Open();
            command = database.CreateCommand(query, Connection);
            for (int i = 0; i < p.Length; i++)
                command.Parameters.Add(p[i]);
            int fields =command.ExecuteNonQuery();
            Close();
            return fields;
        }



        #endregion

        public DataTable ExecuteSQL(string sql, bool b)
        {
            Open();
            command = database.CreateCommand(sql, Connection);
            if (b == true)
            {
                IDbDataAdapter adapter = database.CreateDataAdapter();
                adapter.SelectCommand = command;

                DataSet ds = new DataSet();
                adapter.Fill(ds);
                Close();
                return ds.Tables[0];
            }
            else
            {
                int fields = command.ExecuteNonQuery();
                Close();
            }
            Close();
            Dispose();
            return null;
            
        }

        #region Gọi thủ tục
        
        // Goi thu tuc có chua tham so 

        public DataTable CallSQLProcedure(String Store,MySqlParameter [] p , bool b) 
        {
            Open();

            command = database.CreateCommand(Store, Connection);
            command.CommandType = CommandType.StoredProcedure;
            for(int i=0;i<p.Length;i++)
                command.Parameters.Add(p[i]);
             
            if (b == false)
            {
                command.ExecuteNonQuery();

            }
            else
            {
                IDbDataAdapter adapter = database.CreateDataAdapter();
                adapter.SelectCommand = command;

                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            
            Close();
            Dispose();
            return null;
            
        }
        // Goi thu tuc khong co tham so 
        public DataTable CallSQLProcedure(String Store, bool b) 
        {
            Open();

            command = database.CreateCommand(Store, Connection);
            command.CommandType = CommandType.StoredProcedure;
          
            if (b == false)
            {
                command.ExecuteNonQuery();

            }
            else
            {
                IDbDataAdapter adapter = database.CreateDataAdapter();
                adapter.SelectCommand = command;

                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            
            Close();
            return null;
           
        }
       
        #endregion

        #region CloseReader
        public void CloseReader()
        {
            if (reader != null)
                reader.Close();
        }
        #endregion

        #region Close
        public void Close()
        {
            if (connection.State != ConnectionState.Closed)
                connection.Close();
        }
        #endregion

        #region Dispose
        public void Dispose()
        {
            CloseReader();
            Close();
            if(reader!=null)
            reader.Dispose();
            command.Dispose();
            connection.Dispose();
            connection = null;
            command = null;
        }
        #endregion 

        #endregion


    }
}
